import os
import time

import pandas as pd
import zipfile

excelName4 = '完成.xlsx'

shujutable1 = 'excel_hwn/全店数据订单.xlsx'
mendiantable1 = 'excel_hwn/全部门店.xlsx'
bucanjiatable1 = 'excel_hwn/不参加.xlsx'
quantable1 = 'excel_hwn/费用承担.xlsx'


def startExcel(shujutable, mendiantable, bucanjiatable, quantable):
    # 加载数据
    yield 10, None

    shuju = pd.read_excel(shujutable, dtype={'商品原价销售额': int, '订单编号': str, '商品SKU码': str})
    # 转换数据类型
    shuju = shuju[['订单编号', '优惠活动', '商品SKU码', 'UPC码', '商品原价销售额', '商品名称']]

    yield 30, None

    # 加载数据2
    mendian = pd.read_excel(mendiantable, dtype={'订单号': str})
    bucanjia = pd.read_excel(bucanjiatable, dtype={'商品编码': str})

    # 加载优惠卷明细
    quan = pd.read_excel(quantable)

    yield 60, None

    # 合并两个数据表
    shuju['订单编号'] = shuju['订单编号'].str.strip()
    mendian['订单号'] = mendian['订单号'].str.strip()

    hebing = pd.merge(shuju, mendian, left_on='订单编号', right_on='订单号', how='left').drop_duplicates()

    # 过滤是外卖订单的行
    hebing = hebing[hebing['交易类型'] == "外卖订单"]

    # 把券中所有的活动都添加到合并表中
    activity_columns = quan['活动'].drop_duplicates().str.strip().tolist()

    for column in activity_columns:
        hebing[column] = 0

    # 判断类型函数
    def panduanLeixing(row):
        if len(str(row)) < 4:
            return 5
        return 1 if str(row)[0:2] == '美团' else 0

    # 全部数据过滤
    hebing['代理类型'] = hebing['配送方式'].apply(panduanLeixing)

    # 核心对比函数
    def customOperation(row):
        activities = str(row['优惠活动']).split('/')

        gongyingshang = 0
        gongsichengdan = 0

        for active in activities:
            matches = quan[(quan['活动'] == active.strip()) & ((quan['代理'] == row['代理类型']) | (quan['代理'] == 2))]
            if not matches.empty:
                hebing.at[row.name, active.strip()] = -matches['供应商承担'].sum()
                gongyingshang += matches['供应商承担'].sum()
                gongsichengdan += matches['公司承担费用'].sum()

        hebing.at[row.name, '供应商承担费用'] = -gongyingshang
        hebing.at[row.name, '公司承担费用'] = -gongsichengdan
        hebing.at[row.name, '门店承担费用'] = -(-row['商家活动支出'] - gongyingshang - gongsichengdan)
        return row

    # 所有数据经过自定义计算公式
    hebing.apply(customOperation, axis=1)
    # hebing.to_excel("x.xlsx", index=False)
    # exit(0)
    # 找出不参加的
    hebing['商品SKU码'] = hebing['商品SKU码'].str.strip()
    bucanjia['商品编码'] = bucanjia['商品编码'].str.strip()
    hebing = pd.merge(left=hebing, right=bucanjia, left_on='商品SKU码', right_on='商品编码', how='left',
                      indicator='bcj').drop_duplicates()
    # 只保留参加的
    hebing = hebing[hebing['bcj'] != "both"]

    # 获取订单总金额
    hebing['订单总金额'] = hebing.groupby('订单编号')['商品原价销售额'].transform('sum')

    # 使用广播运算进行计算
    for column in activity_columns:
        # print(column, end='\n')
        hebing[column] = hebing['商品原价销售额'] / hebing['订单总金额'] * hebing[column]

    yield 90, None

    # 生成新excel
    hebing.to_excel(f"excel_hwn/out/{excelName4}", index=False)
    zipname = f'{time.strftime("%Y%m%d%H%M%S", time.localtime())}_arzip.zip'
    with zipfile.ZipFile(f'excel_hwn/out/{zipname}', 'w') as zipf:
        zipf.write(f'excel_hwn/out/{excelName4}', f'{excelName4}')
    yield 100, f'baobiao/download?fileName={zipname}'
    os.remove(f'excel_hwn/out/{excelName4}')
    print(222)



if __name__ == '__main__':
    print(1)
    startExcel(shujutable1, mendiantable1, bucanjiatable1, quantable1)
